<<續>>
MySQL的函數大列9大類:
TIMESTAMPADD()(v5.0.0)例子說明比較快
[code]mysql> SELECT TIMESTAMPADD(MINUTE,1,'2003-01-02');
-> '2003-01-02 00:01:00' --> 第一個參數是單位, 第二個參數是要增加數值, 第三個參數是被加的日期時間
mysql> SELECT TIMESTAMPADD(WEEK,1,'2003-01-02');
-> '2003-01-09'
[/code]
TIMESTAMPDIFF()(v5.0.0): 還是用例子來說明比較快
mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');
-> 3 --> 第一個參數是單位, 第二個、第三個參數是要計算差的日期參數
mysql> SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01');
-> -1
mysql> SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55');
-> 128885
TO_DAYS(): 傳回日期參數是由西元零年1月1日起的第幾天了
UTC_DATE()(v4.1.1): 傳回UTC日期
UTC_TIME()(v4.1.1): 傳回UTC時間
UTC_TIMESTAMP()(v4.1.1): 傳回UTC日期時間
WEEK(): 傳回日期參數於所在的年份內的週數, 例如WEEK('2009-11-12');傳回45
WEEKDAY(): 傳回日期參數所在的週內第幾天, 由星期一(第0天)起算到星期日(第6天), 例如DAYWEEK('2009-11-12');是星期四..傳回3
WEEKOFYEAR()(v4.1.1): 傳回日期參數在該年份內的日曆週數, 由0算到第53週, 例如WEEKOF(YEAR('2009-11-12');回傳46
YEAR(): 傳回日期參數所在的年份
YEARWEEK(): 傳回日期參數所在的年份及週數, 例如YEARWEEK('2009-11-12');傳回200945
全文檢索函數
MySQL提供了相當豐富的全文檢索功能, 這是個可當成單獨研究的課題, 因此我在這裏只以一個簡單的MySQL的全文檢索例子來大致介紹
mysql> CREATE TABLE articles (
-> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
-> title VARCHAR(200),
-> body TEXT,
-> FULLTEXT (title,body) <-- 用FULLTEXT()建立索引鍵及全文檢索的關聯
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO articles (title,body) VALUES
-> ('MySQL Tutorial','DBMS stands for DataBase ...'),
-> ('How To Use MySQL Well','After you went through a ...'),
-> ('Optimizing MySQL','In this tutorial we will show ...'),
-> ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
-> ('MySQL vs. YourSQL','In the following database comparison ...'),
-> ('MySQL Security','When configured properly, MySQL ...');
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM articles
-> WHERE MATCH (title,body) <-- MATCH()及AGAINST()都是MySQL的全文檢索函數
-> AGAINST ('database' IN NATURAL LANGUAGE MODE);
+----+-------------------+------------------------------------------+
| id | title | body |
+----+-------------------+------------------------------------------+
| 5 | MySQL vs. YourSQL | In the following database comparison ... |
| 1 | MySQL Tutorial | DBMS stands for DataBase ... |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)
上面的例子就是在title和body欄位中找到有'database'關鍵字的資料錄出來, 更多的例子:
mysql> SELECT COUNT() FROM articles
-> WHERE MATCH (title,body)
-> AGAINST ('database' IN NATURAL LANGUAGE MODE);
+----------+
| COUNT() |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT
-> COUNT(IF(MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE), 1, NULL))
-> AS count
-> FROM articles;
+-------+
| count |
+-------+
| 2 |
+-------+
1 row in set (0.03 sec)
mysql> SELECT * FROM articles
-> WHERE MATCH (title,body)
-> AGAINST ('MySQL' IN NATURAL LANGUAGE MODE);
Empty set (0.00 sec)
關於MySQL的全文檢索功能, 就先討論到這裏, 以後再專文討論其全部的函數, 有興趣的網有可以參考
http://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html
<<中篇完>>